using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using WP.Commons;
using WP.Controls;
using WP.Modules;
using WP.DB;

namespace WP.Modules
{
    public partial class frmChangeMasterNo : BaseQryRpt
    {
        private enum ChangeMaster
        {
            Supplier = 0,
            Customer,
            SalesPerson
        }
        private ChangeMaster m_cmMaster = ChangeMaster.Supplier;

        public frmChangeMasterNo()
        {
            InitializeComponent();
            this.MinimumSize = this.Size;
        }

        private void frmChangeMasterNo_Load(object sender, EventArgs e)
        {
            if (this.CurrentMenuInfo.iParameter == 0)
            {
                m_cmMaster = ChangeMaster.Supplier;
                lblOldNo.Text = "Old Supplier# :";
                lblNewNo.Text = "New Supplier# :";
                lblCaption1.Text = lblCaption1.Text.Replace("salesperson", "supplier");
                lblCaption2.Text = lblCaption2.Text.Replace("salesperson", "supplier");
            }
            else if (this.CurrentMenuInfo.iParameter == 1)
            {
                m_cmMaster = ChangeMaster.Customer;
                lblOldNo.Text = "Old Customer# :";
                lblNewNo.Text = "New Customer# :";
                lblCaption1.Text = lblCaption1.Text.Replace("salesperson", "customer");
                lblCaption2.Text = lblCaption2.Text.Replace("salesperson", "customer");
            }
            else if (this.CurrentMenuInfo.iParameter == 2)
            {
                m_cmMaster = ChangeMaster.SalesPerson;
                if (SysVar.TraderPrompt.ToUpper() == "Trader".ToUpper())
                {
                    lblCaption1.Text = lblCaption1.Text.Replace("salesperson", "trader");
                    lblCaption2.Text = lblCaption2.Text.Replace("salesperson", "trader");
                    lblOldNo.Text = "Old Trd# :";
                    lblNewNo.Text = "New Trd# :";
                }
                else
                {
                    lblOldNo.Text = "Old Slsp# :";
                    lblNewNo.Text = "New Slsp# :";
                }
            }
        }
        private void btnOK_Click(object sender, EventArgs e)
        {
            if (!ValidCheck()) return;

            if (MsgBoxEx.Show(Msg.Select("OK to proceed?", DataFunctions.GetMsg(this.Name, "MSG_OKToProceed")), "", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.No) return;

            if (m_cmMaster == ChangeMaster.Supplier)
                { if (!ChgSupNo()) return; }
            else if (m_cmMaster == ChangeMaster.Customer)
                { if (!ChgCustNo()) return; }
            else if (m_cmMaster == ChangeMaster.SalesPerson)
                { if (!ChgSlspNo()) return; }
            
            this.Close();
        }
        protected override void ubtLookUp_Click(object sender, EventArgs e)
        {
            string strSQL = "", strFindValue = "", strLookUpField = "", strCaption = "";
            Control con = sender as Control;
            if (con == null) return;
            Control ocon = null;

            DBAccess dbAccess = new DBAccess(false, false);

            switch (con.Name)
            {
                case "ubtOldNo":
                case "ubtNewNo":
                    if (m_cmMaster == ChangeMaster.Supplier)
                    {
                        strSQL = BaseFunctions.GetLookUpSQL("apSupplier");// "select SupNo,SupName,City from apSupplier order by supno"
                        strCaption = "Supplier";
                        strLookUpField = "SupNo";
                    }
                    else if (m_cmMaster == ChangeMaster.Customer)
                    {
                        strSQL = BaseFunctions.GetLookUpSQL("arCustomer");// "select CustNo,CustName from arCustomer order by custno"
                        strCaption = "Customer";
                        strLookUpField = "CustNo";
                    }
                    else if (m_cmMaster == ChangeMaster.SalesPerson)
                    {
                        strSQL = BaseFunctions.GetLookUpSQL("arSalesperson");// "select SlspNo,SlspName from arsalesperson order by SlspNo"
                        strCaption = SysVar.TraderPrompt == string.Empty ? "Trader": SysVar.TraderPrompt;
                        strLookUpField = "SlspNo";
                    }

                    strFindValue = con.Name == "ubtOldNo" ? txtOldNo.Text : txtNewNo.Text;
                    ocon = con.Name == "ubtOldNo" ? txtOldNo : txtNewNo;

                    if (strSQL.Trim() == "") return;
                    break;
                default:
                    return;
            }

            if (mblnLookUpSortByDescription) strSQL = DataFunctions.GetSqlByDescriptionSort(strSQL);
            mblnLookUpSortByDescription = false;

            frmLookUp frmlookup = new frmLookUp(dbAccess, strSQL, strCaption, strFindValue, strLookUpField);

            frmlookup.ShowDialog();
            if (frmlookup.HaveReturnValue)
            {
                ocon.Text = frmlookup.GetReturnValue(strLookUpField);
                ocon.Focus();
            }
            frmlookup.Close();
            dbAccess.CloseCon();
        }
        private void Field_Leave(object sender, EventArgs e)
        {
            Control con = sender as Control;

            // oldno and newno is empty, it's load status.
            if (con == null || this.ActiveControl.Name == btnExit.Name ||
                (txtOldNo.Text == string.Empty && txtNewNo.Text == string.Empty)) return;
            // can click field's lookup button.
            if (con.Name == txtOldNo.Name && this.ActiveControl.Name == ubtOldNo.Name ||
                con.Name == txtNewNo.Name && this.ActiveControl.Name == ubtNewNo.Name) return;

            DBAccess dBAccess = new DBAccess(false, false);

            if (m_cmMaster == ChangeMaster.Supplier)
            {
                con.Text = BaseFunctions.FormatWithBlank(con.Text, 8);
                if (con.Name == txtOldNo.Name)
                    lblOldNoDesc.Text = DataFunctions.GetFieldValue(dBAccess, "apSupplier", "SupName", "SupNo='" + con.Text + "'");
                else
                    lblNewNoDesc.Text = DataFunctions.GetFieldValue(dBAccess, "apSupplier", "SupName", "SupNo='" + con.Text + "'");
            }
            else if (m_cmMaster == ChangeMaster.Customer)
            {
                con.Text = BaseFunctions.FormatWithBlank(con.Text, 8);
                if (con.Name == txtOldNo.Name)
                    lblOldNoDesc.Text = DataFunctions.GetFieldValue(dBAccess, "arCustomer", "CustName", "CustNo='" + con.Text + "'");
                else
                    lblNewNoDesc.Text = DataFunctions.GetFieldValue(dBAccess, "arCustomer", "CustName", "CustNo='" + con.Text + "'");
            }
            else if (m_cmMaster == ChangeMaster.SalesPerson)
            {
                con.Text = BaseFunctions.FormatField(con.Text, "Z3");
                if (con.Name == txtOldNo.Name)
                    lblOldNoDesc.Text = DataFunctions.GetFieldValue(dBAccess, "arSalesperson", "SlspName", "SlspNo='" + con.Text + "'");
                else
                    lblNewNoDesc.Text = DataFunctions.GetFieldValue(dBAccess, "arSalesperson", "SlspName", "SlspNo='" + con.Text + "'");
            }

            if (!Field_Check(dBAccess, con.Name))
                con.Focus();
            dBAccess.CloseCon();
        }

        private bool ChgSupNo()
        {            
            bool bChgSupNo = false;
            string strSql = string.Empty;
            DataRow drSupNo = null;
            DataTable dtTable = null;
            DataRow drCheck = null;
            string strTableName = string.Empty;
            string strFieldName = string.Empty;
            DBAccess dBAccess = new DBAccess(false, false);

            this.Cursor = Cursors.WaitCursor;
            
            try
            {
                dBAccess.BeginTran();

                strSql = "select * from apsupplier where Supno='" + txtOldNo.Text + "'";
                drSupNo = dBAccess.GetDataFirstRow(strSql);
                drSupNo["NewSupNo"] = txtNewNo.Text;

                // tables to change SupNo
                strSql = "select OBJECT_NAME(id) as TableName from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1";
                strSql += "\r\n" + " order by OBJECT_NAME(id)";
                dtTable = dBAccess.GetDataTable(strSql);
                foreach (DataRow drTable in dtTable.Rows)
                {
                    switch (drTable["TableName"].ToString().ToUpper())
                    {
                        case "APSUPPLIER":
                            strTableName = string.Empty;
                            strFieldName = string.Empty;
                            break;
                        default:
                            strTableName = drTable["TableName"].ToString();
                            strFieldName = "SupNo";
                            break;
                    }
                    
                    strSql = "SELECT COLUMNPROPERTY( OBJECT_ID('" + strTableName + "'),'" + strFieldName + "','AllowsNull')";
                    drCheck = dBAccess.GetDataFirstRow(strSql);
                    if (drCheck != null)
                    {
                        if (drCheck[0] != DBNull.Value)
                        {
                            if (strTableName.Substring(0, 3).ToUpper() == "TMP")
                            {
                            }
                            else
                            {
                                strSql = " update " + strTableName + " set " + strFieldName + "='" + txtNewNo.Text + "' where " + strFieldName + "='" + txtOldNo.Text + "'";
                                dBAccess.ExecuteSql(strSql);
                            }
                        }
                    }
                }
                
                drSupNo["SupNo"] = drSupNo["NewSupNo"].ToString();
                if (drSupNo["HeadOffice"].ToString().Trim().ToUpper() == txtOldNo.Text.TrimEnd().ToUpper())
                    drSupNo["HeadOffice"] = drSupNo["NewSupNo"].ToString();
                drSupNo["NewSupNo"] = string.Empty;
                
                DataFunctions.UpdateTable(dBAccess, "SupNo,HeadOffice,NewSupNo", "apSupplier", drSupNo);
                
                dBAccess.CommitTranAndCloseCon();

            }
            catch(Exception ex)
            {
                dBAccess.RollbackTranAndClose();
                MsgBoxEx.Show(ex.Message, SysVar.WarnTitle);
            }
            finally
            {
                this.Cursor = Cursors.Default;
                MsgBoxEx.Show(Msg.Select("Supplier number processing complete.", DataFunctions.GetMsg(this.Name, "MSG_SupNoProcessingComplete")), SysVar.InformationTitle);
            }

            bChgSupNo = true;
            return bChgSupNo;

        }
        private bool ChgCustNo() // New CustNo Can exists in arCustomer
        {
            bool bChgCustNo = false;
            string strSql = string.Empty;
            string strTableString = string.Empty;
            string strConditionString = string.Empty;
            string[] strTableArray = null;
            string[] strConditionArray = null;
            int i = 0;
            bool blnNewNoInCustTable = false;
            bool blnNewNoInCustTotTable = false;
            string strTableName = string.Empty;
            string strFieldName = string.Empty;
            string strFields = string.Empty; // Field for update
            DataTable dtTable = null;
            DataRow drCheck = null;
            DataRow drCustNo = null;
            DBAccess dBAccess = new DBAccess(false, false);
            
            this.Cursor = Cursors.WaitCursor;
            
            strSql = "select Recid from arCustTotals where CustNo='" + txtNewNo.Text + "'";
            dtTable = dBAccess.GetDataTable(strSql);
            if (dtTable.Rows.Count > 0) // in arCustTotals table
                blnNewNoInCustTotTable = true;

            #region get update table and field
            strSql = "select OBJECT_NAME(id) as TableName from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1";
            strSql += "\r\n" + " order by OBJECT_NAME(id)";
            dtTable = dBAccess.GetDataTable(strSql);
            foreach (DataRow drTable in dtTable.Rows)
            {
                switch (drTable["TableName"].ToString().ToUpper())
                {
                    case "ARCUSTTOTALS":
                        if (!blnNewNoInCustTotTable)
                        {
                            strTableName = drTable["TableName"].ToString();
                            strFieldName = "CustNo";
                        }
                        else
                        {
                            strTableName = "";
                            strFieldName = "";
                        }
                        break;
                    default:
                        strTableName = drTable["TableName"].ToString();
                        strFieldName = "CustNo";
                        break;
                }
                
                strSql = "SELECT COLUMNPROPERTY( OBJECT_ID('" + strTableName + "'),'" + strFieldName + "','AllowsNull')";
                drCheck = dBAccess.GetDataFirstRow(strSql);
                if (drCheck != null)
                {
                    if (drCheck[0] != DBNull.Value)
                    {
                        strTableString += strTableName + ",";
                        strConditionString += strFieldName + ",";
                    }
                }
            }
            strTableArray = strTableString.TrimEnd(',').Split(',');
            strConditionArray = strConditionString.TrimEnd(',').Split(',');
            #endregion

            try
            {
                dBAccess.BeginTran();

                strSql = "select Recid from arCustomer where CustNo='" + txtNewNo.Text + "'";
                dtTable = dBAccess.GetDataTable(strSql);
                if (dtTable.Rows.Count > 0) // new custno in arCustomer table
                {
                    blnNewNoInCustTable = true;
                    strSql = "delete from arCustomer where CustNo='" + txtOldNo.Text + "'"; // delete old custno
                    dBAccess.ExecuteSql(strSql);
                }

                for (i = 0; i < strTableArray.Length; i++)
                {
                    strSql = "update [" + strTableArray[i] + "] set " + strConditionArray[i] + "='" + txtNewNo.Text + "' where " + strConditionArray[i] + "='" + txtOldNo.Text + "'";
                    dBAccess.ExecuteSql(strSql);
                }

                if (blnNewNoInCustTotTable) // new custno in arCustTotals table
                {
                    strSql = "select * from arCustTotals where CustNo='" + txtOldNo.Text + "'"; // old cust data
                    dtTable = dBAccess.GetDataTable(strSql);

                    strSql = "select * from arCustTotals where CustNo='" + txtNewNo.Text + "'"; // new cust data
                    drCustNo = dBAccess.GetDataFirstRow(strSql);

                    if (dtTable.Rows.Count > 0) // old customer has data in arCustTotals table
                    {
                        // add old customer data to new customer data
                        for (i = 0; i< dtTable.Columns.Count; i++) // except for recid,custno,RecStamp
                        {
                            if (drCustNo.Table.Columns[i].ColumnName.Trim().ToUpper() == ("recid").ToUpper() ||
                                drCustNo.Table.Columns[i].ColumnName.Trim().ToUpper() == ("custno").ToUpper() ||
                                drCustNo.Table.Columns[i].ColumnName.Trim().ToUpper() == ("RecStamp").ToUpper())
                            {
                            }
                            else
                            {
                                drCustNo[i] = drCustNo[i].ToString() + dtTable.Rows[0][i].ToString();
                                strFields += drCustNo.Table.Columns[i].ColumnName;
                            }
                        }
                        DataFunctions.UpdateTable(dBAccess, strFields, "arCustTotals", drCustNo);

                        // delete old customer data from arCustTotals
                        strSql = "delete from arCustTotals where CustNo='" + txtOldNo.Text + "'";
                        dBAccess.ExecuteSql(strSql);
                    }
                }

                strSql = "select * from arCustomer where custno='" + txtNewNo.Text + "'";
                drCustNo = dBAccess.GetDataFirstRow(strSql);
                if (drCustNo != null)
                {
                    drCustNo["Headoffice"] = drCustNo["CustNo"].ToString();
                    drCustNo["NewCustNo"] = string.Empty;
                    DataFunctions.UpdateTable(dBAccess, "Headoffice,NewCustNo", "arCustomer", drCustNo);
                }

                dBAccess.CommitTranAndCloseCon();

            }
            catch(Exception ex)
            {
                dBAccess.RollbackTranAndClose();
                MsgBoxEx.Show(ex.Message, SysVar.WarnTitle);
            }
            finally
            {
                this.Cursor = Cursors.Default;
                MsgBoxEx.Show(Msg.Select("Customer number processing complete.", DataFunctions.GetMsg(this.Name, "MSG_CustNoProcessingComplete")), SysVar.InformationTitle);
            }

            bChgCustNo = true;
            return bChgCustNo;
        }
        private bool ChgSlspNo()
        {

            bool bChgSlspNo = false;
            string strSql = string.Empty;
            string strTableString = string.Empty;
            string strConditionString = string.Empty;
            string[] strTableArray = null;
            string[] strConditionArray = null;
            int i = 0;
            int j = 0;
            bool blnNewNoInSlspTable = false;
            
            string strTableName = string.Empty;
            string strFieldName = string.Empty;
            DataRow drSlspNo = null;
            DataTable dtTable = null;
            DataRow drCheck = null;
            DBAccess dBAccess = new DBAccess(false, false);

            this.Cursor = Cursors.WaitCursor;
            
            #region get update table and field
            strSql = "select OBJECT_NAME(id) as TableName from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1";
            strSql += "\r\n" + " order by OBJECT_NAME(id)";
            dtTable = dBAccess.GetDataTable(strSql);
            foreach (DataRow drTable in dtTable.Rows)
            {
                switch (drTable["TableName"].ToString().ToUpper())
                {
                    case "POORDERHDR":
                        strFieldName = "Trader";
                        break;
                    case "OEORDERHDR":
                    case "OERETURNHDR":
                    case "OEQUOTEHDR":
                        strFieldName = "SlpNo1";
                        break;
                    case "ARHISRPT":
                        strFieldName = "SlspNo1";
                        break;
                    case "ARSALESHIST":
                    case "IMINVHIST":
                    case "OETRFHDR":
                    case "IMTRFHDR":
                        strFieldName = "SlpNo";
                        break;
                    case "ARCASHHDR":
                    case "ARADJUSTHDR":
                    case "APACCRUINVSURDISC":
                    case "APACCRUINVSURDISCFORCLEAR":
                        strFieldName = "SlspNo01";
                        break;
                    case "MLOCATION":
                        strFieldName = "OwnBySlspNo";
                        break;
                    case "MUSER":
                        strFieldName = "Salesperson";
                        break;
                    default:
                        strFieldName = "SlspNo";
                        break;
                }
                strTableName = drTable["TableName"].ToString();
                
                strSql = "SELECT COLUMNPROPERTY( OBJECT_ID('" + strTableName + "'),'" + strFieldName + "','AllowsNull')";
                drCheck = dBAccess.GetDataFirstRow(strSql);
                if (drCheck != null)
                {
                    if (drCheck[0] != DBNull.Value)
                    {
                        switch (drTable["TableName"].ToString().ToUpper())
                        {
                            case "OEORDERHDR":
                            case "OERETURNHDR": // need update SlpNo1,InsideSlpNo,the no use fileds (SlpNo2,SlpNo3) don't need to update 
                                for (j = 1; j<= 2; j++)
                                {
                                    strTableString += strTableName + ",";
                                    if (j == 1)
                                        strConditionString += "SlpNo1,";
                                    else
                                        strConditionString += "InsideSlpNo,";
                                }
                                break;
                            case "OEQUOTEHDR": // need update SlpNo1,InsideSlpNo
                                for (j = 1; j<= 2; j++)
                                {
                                    strTableString += strTableName + ",";
                                    if (j == 1)
                                        strConditionString += "SlpNo1,";
                                    else
                                        strConditionString += "InsideSlpNo,";
                                }
                                break;
                            case "ARSHIPTO": // need update SlpNo1,insideslspno
                                for (j = 1; j<= 2; j++)
                                {
                                    strTableString += strTableName + ",";
                                    if (j == 1)
                                        strConditionString += "SlspNo,";
                                    else
                                        strConditionString += "insideslspno,";
                                }
                                break;
                            case "ARHISRPT": // need update SlspNo1,SlspNo2
                                for (j = 1; j<= 2; j++)
                                {
                                    strTableString += strTableName + ",";
                                    strConditionString += "SlspNo" + j.ToString() + ",";
                                }
                                break;
                            case "ARCASHHDR":
                            case "ARADJUSTHDR": // need update SlspNo01,SlspNo02,SlspNo03,SlspNo04
                                for (j = 1; j<= 4; j++)
                                {
                                    strTableString += strTableName + ",";
                                    strConditionString += "SlspNo0" + j.ToString() + ",";
                                }
                                break;
                            //case "APACCRUINVSURDISC":
                            case "APACCRUINVSURDISCFORCLEAR": // need update SlspNo01,SlspNo02,SlspNo03
                                for (j = 1; j<= 3; j++)
                                {
                                    strTableString += strTableName + ",";
                                    strConditionString += "SlspNo0" + j.ToString() + ",";
                                }
                                break;
                            case "APACCRUINVSURDISC": // need update SlspNo01,SlspNo02,SlspNo03,PurchSlspNo01,PurchSlspNo02,PurchSlspNo03
                                for (j = 1; j<= 3; j++)
                                {
                                    strTableString += strTableName + ",";
                                    strConditionString += "SlspNo0" + j.ToString() + ",";
                                }
                                for (j = 1; j<= 3; j++)
                                {
                                    strTableString += strTableName + ",";
                                    strConditionString += "PurchSlspNo0" + j.ToString() + ",";
                                }
                                break;
                            case "ARSLSPHIST": // need update SlspNo,PurchSlspNo
                                for (j = 1; j<= 2; j++)
                                {
                                    strTableString += strTableName + ",";
                                    if (j == 1)
                                        strConditionString += "SlspNo,";
                                    else
                                        strConditionString += "PurchSlspNo,";
                                }
                                break;
                            case "OETRFHDR": // need update SlpNo,SlpNo1
                                for (j = 1; j<= 2; j++)
                                {
                                    strTableString += strTableName + ",";
                                    if (j == 1)
                                        strConditionString += "SlpNo,";
                                    else
                                        strConditionString += "SlpNo1,";
                                }
                                break;
                            case "ARCUSTOMER": // need update SlspNo,MoreSlspNo01, MoreSlspNo02, MoreSlspNo03
                                for (j = 1; j<= 4; j++)
                                {
                                    strTableString += strTableName + ",";
                                    if (j != 4)
                                        strConditionString += "MoreSlspNo0" + j.ToString() + ",";
                                    else
                                        strConditionString += "SlspNo,";
                                }
                                break;
                            default: // only one field need to update
                                strTableString += strTableName + ",";
                                strConditionString += strFieldName + ",";
                                break;
                        }
                    }
                }
                
            }
            strTableArray = strTableString.TrimEnd(',').Split(',');
            strConditionArray = strConditionString.TrimEnd(',').Split(',');
            #endregion

            try
            {
                dBAccess.BeginTran();
            
                strSql = "select Recid from arSalesperson where SlspNo='" + txtNewNo.Text + "'";
                dtTable = dBAccess.GetDataTable(strSql);
                if (dtTable.Rows.Count > 0) // new trader in arsalesperson table
                    blnNewNoInSlspTable = true;
                
                for (i = 0; i< strTableArray.Length; i++)
                {
                    switch (strTableArray[i].ToUpper())
                    {
                        case "ARSALESPERSON":
                            if (!blnNewNoInSlspTable)
                            {
                                strSql = "update [" + strTableArray[i] + "] set " + strConditionArray[i] + "='" + txtNewNo.Text + "' where " + strConditionArray[i] + "='" + txtOldNo.Text + "'";
                                dBAccess.ExecuteSql(strSql);
                            }
                            break;
                        default:
                            strSql = "update [" + strTableArray[i] + "] set " + strConditionArray[i] + "='" + txtNewNo.Text + "' where " + strConditionArray[i] + "='" + txtOldNo.Text + "'";
                            dBAccess.ExecuteSql(strSql);
                            break;
                    }
                }

                if (blnNewNoInSlspTable) // new trader in arsalesperson table
                {
                    strFieldName = string.Empty;
                    for (j = 1; j<= 12; j++)
                    {
                        strFieldName = (strFieldName == string.Empty? "CommAmt" + string.Format("{0:00}", j): strFieldName + ",CommAmt" + string.Format("{0:00}", j));
                        strFieldName = strFieldName + ",PrYrCommAmt" + string.Format("{0:00}", j);
                        strFieldName = strFieldName + ",Sales" + string.Format("{0:00}", j);
                        strFieldName = strFieldName + ",Cost" + string.Format("{0:00}", j);
                        strFieldName = strFieldName + ",PrYrSales" + string.Format("{0:00}", j);
                        strFieldName = strFieldName + ",PrYrCost" + string.Format("{0:00}", j);
                        strFieldName = strFieldName + ",PrPrYrSales" + string.Format("{0:00}", j);
                        strFieldName = strFieldName + ",PrPrYrCost" + string.Format("{0:00}", j);
                        strFieldName = strFieldName + ",MktCost" + string.Format("{0:00}", j);
                        strFieldName = strFieldName + ",PrMktCost" + string.Format("{0:00}", j);
                        strFieldName = strFieldName + ",PRPrMktCost" + string.Format("{0:00}", j);
                    }
                    strFieldName = strFieldName + ",CommAmt13,MktCost13";
                    
                    strSql = "select " + strFieldName + " from arSalesperson where SlspNo='" + txtOldNo.Text + "'"; // old slsp data
                    dtTable = dBAccess.GetDataTable(strSql);

                    strSql = "select " + strFieldName + " from arSalesperson where SlspNo='" + txtNewNo.Text + "'"; // new slsp data
                    drSlspNo = dBAccess.GetDataFirstRow(strSql);
                    if (dtTable.Rows.Count > 0) // old slsp has data in arSalesperson table
                    {
                        // add old slsp data to new slsp data
                        for (i = 0; i < dtTable.Columns.Count; i++)
                        {
                            drSlspNo[i] = CvtFunc.ToDouble(drSlspNo[i]) + CvtFunc.ToDouble(dtTable.Rows[0][i]);
                        }
                        DataFunctions.UpdateTable(dBAccess, strFieldName, "arSalesperson", drSlspNo);
                        
                        strSql = "delete from arSalesperson where SlspNo='" + txtOldNo.Text + "'"; // delete old SlspNo
                        dBAccess.ExecuteSql(strSql);
                    }
                }
               
                //AddLog gcn, "Change slsp# from " + txtOldNo.Text + " to " + txtNewNo.Text, "AR"
                dBAccess.CommitTranAndCloseCon();

            }
            catch (Exception ex)
            {
                dBAccess.RollbackTranAndClose();
                MsgBoxEx.Show(ex.Message, SysVar.WarnTitle);
            }
            finally
            {
                this.Cursor = Cursors.Default;
                MsgBoxEx.Show(Msg.Select("Salesperson number processing complete.", DataFunctions.GetMsg(this.Name, "MSG_SlspNoProcessingComplete")), SysVar.InformationTitle);
            }

            bChgSlspNo = true;
            return bChgSlspNo;
        }

        private bool ValidCheck()
        {
            bool bValidCheck = false;
            DBAccess dBAccess = new DBAccess(false, false);

            if (!Field_Check(dBAccess, txtOldNo.Name))
            {
                txtOldNo.Focus();
                return bValidCheck;
            }

            if (!Field_Check(dBAccess, txtNewNo.Name))
            {
                txtNewNo.Focus();
                return bValidCheck;
            }

            bValidCheck = true;
            return bValidCheck;
        }
        private bool Field_Check(DBAccess dBAccess, string strControlName)
        {
            bool bCheck = false;
            string strSql = string.Empty;
            DataRow drCheck = null;
            
            if (m_cmMaster == ChangeMaster.Supplier)
            {
                #region Check Supplier
                if (strControlName == "txtOldNo")
                {
                    strSql = "select * from apSupplier where Supno='" + txtOldNo.Text + "'";
                    drCheck = dBAccess.GetDataFirstRow(strSql);
                    if (drCheck == null)
                    {
                        MsgBoxEx.Show(Msg.Select("Supplier number does not exist!", DataFunctions.GetMsg(dBAccess, "SupplierNotExist")), SysVar.WarnTitle);
                        return bCheck;
                    }
                    else
                    {
                        if (drCheck["NewSupNo"].ToString().Trim() != string.Empty)
                        {
                            MsgBoxEx.Show(Msg.Select("Finish changing supplier# to" + " " + drCheck["NewSupNo"].ToString(), DataFunctions.GetMsg(dBAccess, "NewSupNo") + " " + drCheck["NewSupNo"].ToString()), SysVar.WarnTitle);
                            txtNewNo.Text = drCheck["NewSupNo"].ToString();
                            bCheck = true;
                            return bCheck;
                        }
                    }
                }
                else if (strControlName == "txtNewNo")
                {
                    if (txtNewNo.Text.Trim() == string.Empty)
                    {
                        MsgBoxEx.Show(Msg.Select("Must enter supplier number!", DataFunctions.GetMsg(dBAccess, "EnterSupNo")), SysVar.WarnTitle);
                        return bCheck;
                    }
                    
                    if (txtOldNo.Text == txtNewNo.Text)
                    {
                        MsgBoxEx.Show(Msg.Select("New supplier is the same as the old one.", DataFunctions.GetMsg(dBAccess, "SameSupNo")), SysVar.WarnTitle);
                        return bCheck;
                    }
                    else
                    {
                        strSql = "select recid from apSupplier where Supno='" + txtNewNo.Text + "'";
                        drCheck = dBAccess.GetDataFirstRow(strSql);
                        if (drCheck != null)
                        {
                            MsgBoxEx.Show(Msg.Select("Supplier already on file.", DataFunctions.GetMsg(dBAccess, "SupplierExist")), SysVar.WarnTitle);
                            return bCheck;
                        }
                    }
                }
                #endregion
            }
            else if (m_cmMaster == ChangeMaster.Customer)
            {
                #region Check Customer
                if (strControlName == "txtOldNo")
                {
                    strSql = "select * from arCustomer where custno='" + txtOldNo.Text + "'";
                    drCheck = dBAccess.GetDataFirstRow(strSql);
                    if (drCheck == null)
                    {
                        MsgBoxEx.Show(Msg.Select("Customer number does not exist!", DataFunctions.GetMsg(dBAccess, "CustomerNotExist")), SysVar.WarnTitle);
                        return bCheck;
                    }
                    else
                    {
                        if (drCheck["NewCustNo"].ToString().Trim() != string.Empty)
                        {
                            MsgBoxEx.Show(Msg.Select("Finish changing customer# to" + " " + drCheck["NewCustNo"].ToString(), DataFunctions.GetMsg(dBAccess, "NewCustNo") + " " + drCheck["NewCustNo"].ToString()), SysVar.WarnTitle);
                            txtNewNo.Text = drCheck["NewCustNo"].ToString();
                            bCheck = true;
                            return bCheck;
                        }
                    }
                }
                else if (strControlName == "txtNewNo")
                {
                    if (txtNewNo.Text.Trim() == string.Empty)
                    {
                        MsgBoxEx.Show(Msg.Select("Must enter customer number!", DataFunctions.GetMsg(dBAccess, "EnterCustNo")), SysVar.WarnTitle);
                        return bCheck;
                    }
                    
                    if (txtOldNo.Text == txtNewNo.Text)
                    {
                        MsgBoxEx.Show(Msg.Select("New customer is the same as the old one.", DataFunctions.GetMsg(dBAccess, "SameCustNo")), SysVar.WarnTitle);
                        return bCheck;
                    }
                    else
                    {
                        strSql = "select recid from arCustomer where custno='" + txtNewNo.Text + "'";
                        drCheck = dBAccess.GetDataFirstRow(strSql);
                        if (drCheck != null)
                        {
                            MsgBoxEx.Show(Msg.Select("Customer already on file.", DataFunctions.GetMsg(dBAccess, "CustomerExist")), SysVar.WarnTitle);
                            return bCheck;
                        }
                    }
                }
                #endregion
            }
            else if (m_cmMaster == ChangeMaster.SalesPerson)
            {
                #region Check SalesPerson
                if (strControlName == "txtOldNo")
                {
                    strSql = "select * from arSalesperson where SlspNo='" + txtOldNo.Text + "'";
                    drCheck = dBAccess.GetDataFirstRow(strSql);
                    if (drCheck == null)
                    {
                        MsgBoxEx.Show(DataFunctions.GetMsg(dBAccess, "SalesPersonNotExist"), SysVar.WarnTitle);
                        return bCheck;
                    }
                }
                else if (strControlName == "txtNewNo")
                {
                    if (txtNewNo.Text.Trim() == string.Empty)
                    {
                        MsgBoxEx.Show(Msg.Select("Must enter salesperson number!", DataFunctions.GetMsg(dBAccess, this.Name, "MSG_EnterSlspNo")), SysVar.WarnTitle);
                        return bCheck;
                    }
                    
                    if (txtOldNo.Text == txtNewNo.Text)
                    {
                        MsgBoxEx.Show(Msg.Select("New salesperson is the same as the old one.", DataFunctions.GetMsg(dBAccess, this.Name, "MSG_SameSlspNo")), SysVar.WarnTitle);
                        return bCheck;
                    }
                    else
                    {
                        strSql = "select recid from arSalesperson where SlspNo='" + txtNewNo.Text + "'";
                        drCheck = dBAccess.GetDataFirstRow(strSql);
                        if (drCheck != null)
                        {
                            MsgBoxEx.Show(Msg.Select("Salesperson already on file.", DataFunctions.GetMsg(dBAccess, this.Name, "MSG_SalesPersonExist")), SysVar.WarnTitle);
                            return bCheck;
                        }
                    }
                }
                #endregion
            }

            return true;
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnHelp_Click(object sender, EventArgs e)
        {
            base.OnHelp();
        }

    }
}